package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Item;
import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.ItemDAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class ItemDAOImpl implements ItemDAO {

    //2.准备sql语句
    private static final String getItemListByProductString = " SELECT I.ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS \"product.productId\",NAME AS \"product.name\",DESCN AS \"product.description\",CATEGORY AS \"product.categoryId\", STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5 FROM ITEM I, PRODUCT P WHERE P.PRODUCTID = I.PRODUCTID AND I.PRODUCTID = ?";
    private static final String getItemString = "select I.ITEMID, LISTPRICE, UNITCOST, SUPPLIER AS supplierId, I.PRODUCTID AS \"product.productId\",NAME AS \"product.name\",DESCN AS \"product.description\",CATEGORY AS \"product.categoryId\",STATUS,ATTR1 AS attribute1,ATTR2 AS attribute2,ATTR3 AS attribute3,ATTR4 AS attribute4,ATTR5 AS attribute5,QTY AS quantity from ITEM I, INVENTORY V, PRODUCT P where P.PRODUCTID = I.PRODUCTID and I.ITEMID = V.ITEMID and I.ITEMID = ?";
    private static final String getInventoryQuantityString = " SELECT QTY AS value FROM INVENTORY WHERE ITEMID = ?";
    private static final String updateInventoryQuantityString = " UPDATE INVENTORY SET QTY = QTY - ? WHERE ITEMID = ?";

    @Override
    public void updateInventoryQuantity(Map<String, Object> param) {
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(updateInventoryQuantityString);

            String itemId = param.keySet().iterator().next();
            Integer increment = (Integer) param.get(itemId);
            pStatement.setInt(1, increment.intValue());
            pStatement.setString(2, itemId);
            pStatement.executeUpdate();

            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
    }

    @Override
    public int getInventoryQuantity(String itemId) {
        int result = -1;
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(getInventoryQuantityString);
            pStatement.setString(1, itemId);
            //4.执行结果
            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()){
               result = resultSet.getInt(1);
            }

            DBUtil.closeResultSet(resultSet);//关闭结果集
            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
        return result;
    }

    @Override
    public List<Item> getItemListByProduct(String productId) {
        List<Item> itemList = new ArrayList<Item>();
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(getItemListByProductString);
            pStatement.setString(1, productId);
            //4.执行结果
            ResultSet resultSet = pStatement.executeQuery();

            while (resultSet.next()){
                Item item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                itemList.add(item);
            }

            DBUtil.closeResultSet(resultSet);//关闭结果集
            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
        return itemList;
    }

    @Override
    public Item getItem(String itemId) {
        Item item = null;
        try {
            //1.获得连接
            Connection connection = DBUtil.getConnection();
            //3.执行对象
            PreparedStatement pStatement = connection.prepareStatement(getItemString);
            pStatement.setString(1, itemId);
            //4.执行结果
            ResultSet resultSet = pStatement.executeQuery();

            if (resultSet.next()){
                item = new Item();
                item.setItemId(resultSet.getString(1));
                item.setListPrice(resultSet.getBigDecimal(2));
                item.setUnitCost(resultSet.getBigDecimal(3));
                item.setSupplierId(resultSet.getInt(4));
                Product product = new Product();
                product.setProductId(resultSet.getString(5));
                product.setName(resultSet.getString(6));
                product.setDescription(resultSet.getString(7));
                product.setCategoryId(resultSet.getString(8));
                item.setProduct(product);
                item.setStatus(resultSet.getString(9));
                item.setAttribute1(resultSet.getString(10));
                item.setAttribute2(resultSet.getString(11));
                item.setAttribute3(resultSet.getString(12));
                item.setAttribute4(resultSet.getString(13));
                item.setAttribute5(resultSet.getString(14));
                item.setQuantity(resultSet.getInt(15));
            }

            DBUtil.closeResultSet(resultSet);//关闭结果集
            DBUtil.closePreparedStatement(pStatement);//关闭执行对象
            DBUtil.closeConnection(connection);//关闭连接
        }catch (Exception e){
            e.printStackTrace();//打印异常
        }
        return item;
    }
}
